﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Windows.Forms;

namespace TestDatabase.DataLogic 
{
    public partial class FVE 
    {
        private const String databaseFileName = @"c:\!\fve.db";
        private SQLiteConnection slConnection;
        private static String slConnectionString = @"Data Source={0};Version=3;";

        public SQLiteConnection Connection { get { return slConnection; } }

        public void OpenDatabase(string FileName = databaseFileName)
        {
            bool create = false;

            if (!File.Exists(FileName))
            {
                SQLiteConnection.CreateFile(FileName);
                create = true;
            }

            slConnection = new SQLiteConnection(String.Format(slConnectionString, FileName));
            slConnection.Open();

            if (create) CreateDatabase();
        }

        public void GetData(string FileName, SQLiteTransaction tran)
        {
            const string insertPhrase =
@"INSERT INTO 
    [BasicData]
(
    [date_time],
    [address],
    [offset],

    [value1],
    [value2],
    [value3],
    [value4],
    [value5],
    [value6],
    [value7],
    [value8],
    [value9],
    [value10],

    [x_created]
)
Values
(
    '{0}',
    '{1}',
    {2},

    {3},
    {4},
    {5},
    {6},
    {7},
    {8},
    {9},
    {10},
    {11},
    {12},

    '{13}'
)";

            FileStream aFile = new FileStream(FileName, FileMode.Open);

            // check if File processed and size is valid -----------------> PROCEDURE

            /*
            public static List<string> GetImportedFileList()
                {
                    List<string> ImportedFiles = new List<string>();                        
                    using (SQLiteConnection connect = new SQLiteConnection(@"Data Source=C:\Documents and Settings\js91162\Desktop\CMMData.db3"))
                    {
                        connect.Open();
                        using (SQLiteCommand fmd = connect.CreateCommand())
                        {
                            fmd.CommandText = @"SELECT DISTINCT FileName FROM Import";
                            SQLiteDataReader r = fmd.ExecuteReader();
                            while (r.Read()) 
                            {
                                string FileNames = (string)r["FileName"];

                                ImportedFiles.Add(FileNames);
                            }                    

                            connect.Close();
                    }
                }
                return ImportedFiles;
            }
            */

            StreamReader sr = new StreamReader(aFile);

            string strLine;
            string[] strArray;
            char[] charArray = new char[] { ';' };

            int myInt;

            strLine = sr.ReadLine();
            strArray = strLine.Split(charArray);

            strLine = sr.ReadLine();

            bool IsHeader = true;
            int test = 0;
            string dateTime = String.Empty;

            var testFN = Path.GetFileName(FileName);

            SQLiteCommand comm = slConnection.CreateCommand();

            var ci = CultureInfo.InvariantCulture.Clone() as CultureInfo;
            ci.NumberFormat.NumberDecimalSeparator = ".";

            while (strLine != null)
            {
                if (strLine.Trim() == "[Start]")
                {
                    IsHeader = false;
                    strLine = sr.ReadLine();
                    continue;
                }
                
                //Debug.WriteLine(strLine);

                if (IsHeader)
                {
                    strLine = sr.ReadLine();
                    continue;
                }

                if (strLine.Trim() == String.Empty || strLine.Trim().Length <= 2)
                {
                    strLine = sr.ReadLine();
                    continue;
                }

                if (!(int.TryParse(strLine[0].ToString(), out myInt) && int.TryParse(strLine[1].ToString(), out myInt)))
                {
                    strLine = sr.ReadLine();
                    continue;
                }

                strArray = strLine.Split(charArray);

                if (strArray.Length <= 2)
                {
                    strLine = sr.ReadLine();
                    continue;
                }

                //dateTime = String.Empty;

                dateTime = String.Format("20{2}-{1}-{0} {3}", testFN.Substring(12, 2), testFN.Substring(10, 2), testFN.Substring(8, 2), strArray[0]);

                comm.Transaction = tran;
                comm.CommandText = String.Format
                    (
                        insertPhrase, 
                        dateTime, 
                        strArray.Length > 2 ? strArray[2] : "NULL",
                        strArray[1], 
                        strArray.Length > 3 && strArray[3] != String.Empty ? Convert.ToDouble(strArray[3], ci).ToString(ci) : "NULL",
                        strArray.Length > 4 && strArray[4] != String.Empty ? Convert.ToDouble(strArray[4], ci).ToString(ci) : "NULL",
                        strArray.Length > 5 && strArray[5] != String.Empty ? Convert.ToDouble(strArray[5], ci).ToString(ci) : "NULL",
                        strArray.Length > 6 && strArray[6] != String.Empty ? Convert.ToDouble(strArray[6], ci).ToString(ci) : "NULL",
                        strArray.Length > 7 && strArray[7] != String.Empty ? Convert.ToDouble(strArray[7], ci).ToString(ci) : "NULL",
                        strArray.Length > 8 && strArray[8] != String.Empty ? Convert.ToDouble(strArray[8], ci).ToString(ci) : "NULL",
                        strArray.Length > 9 && strArray[9] != String.Empty ? Convert.ToDouble(strArray[9], ci).ToString(ci) : "NULL",
                        strArray.Length > 10 && strArray[10] != String.Empty ? Convert.ToDouble(strArray[10], ci).ToString(ci) : "NULL",
                        strArray.Length > 11 && strArray[11] != String.Empty ? Convert.ToDouble(strArray[11], ci).ToString(ci) : "NULL",
                        strArray.Length > 12 && strArray[12] != String.Empty ? Convert.ToDouble(strArray[12], ci).ToString(ci) : "NULL",
                        DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")
                    );
                comm.CommandType = CommandType.Text;

                comm.ExecuteNonQuery();

                //for (int i = 0; i <= strArray.GetUpperBound(0); i++)
                //{
                //    dr[i] = strArray[i].Trim();
                //}

                //table.Rows.Add(dr);
                test++;

                strLine = sr.ReadLine();
            }

            InsertToFileList(FileName, aFile.Length, new DateTime(Convert.ToInt16(String.Format("20{0}", testFN.Substring(8, 2))), Convert.ToInt16(testFN.Substring(10, 2)), Convert.ToInt16(testFN.Substring(12, 2)), 0, 0, 0));

            sr.Close();
        }

        private void InsertToFileList(string FileName, long FileSize, DateTime FileDate)
        {
            const string insertPhrase =
@"INSERT INTO 
    [FileList]
(
    [file_name],
    [file_size],
    [file_date],
    [x_created]
)
Values
(
    '{0}',
    {1},
    '{2}',
    '{3}'
)";

            SQLiteCommand comm = slConnection.CreateCommand();
            comm.CommandText = String.Format(insertPhrase, Path.GetFileName(FileName), FileSize, FileDate.ToString("yyyy-MM-dd HH:mm:ss"), DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            
            comm.ExecuteNonQuery();
        }

        private void CreateDatabase()
        {
            const String test =
@"Create Table [BasicData]
(
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    date_time       DATE NOT NULL,
    address         TEXT NOT NULL,
    offset          INTEGER,
    value1          NUMERIC,
    value2          NUMERIC, 
    value3          NUMERIC, 
    value4          NUMERIC, 
    value5          NUMERIC, 
    value6          NUMERIC, 
    value7          NUMERIC, 
    value8          NUMERIC, 
    value9          NUMERIC, 
    value10         NUMERIC, 
    x_created       DATE
);

Create Table [FileList]
(
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    file_name       Text NOT NULL,
    file_size       INTEGER,
    file_date		DATE,
    x_created       DATE
)";

            SQLiteCommand comm = slConnection.CreateCommand();
            comm.CommandText = test;
            comm.ExecuteNonQuery();
        }

        partial class DayTableDataTable
        {
        }

        public DataTable GetDayData(DateTime date, int stridac = 1, int data = 2)
        {
			const String selectData =
@"Select 
	time([date_time]) As [date_time], 
	IFNULL([value1],0) As [value1], 
	IFNULL([value2],0) As [value2], 
	IFNULL([value3],0) As [value3], 
	IFNULL([value4],0) As [value4], 
	IFNULL([value5],0) As [value5], 
	IFNULL([value6],0) As [value6], 
	IFNULL([value7],0) As [value7], 
	IFNULL([value8],0) As [value8], 
	IFNULL([value9],0) As [value9], 
	IFNULL([value10],0) As [value10] 
From 
	[BasicData] 
Where 
	date([date_time]) = '{0:yyyy-MM-dd}' And 
	[address] = '{1}/{2}'";
			
            DataTable dt = new DataTable();
            SQLiteCommand command = slConnection.CreateCommand();
            command.CommandText = String.Format(selectData, date, stridac, data);
            
            //Debug.WriteLine(command.CommandText);
            
            SQLiteDataAdapter db = new SQLiteDataAdapter(command);
            db.Fill(dt);
            
            return dt;
        }
        
        public DataTable GetDateList()
        {
        	DataTable dt = new DataTable();
        	SQLiteCommand command = slConnection.CreateCommand();
        	command.CommandText = @"Select [file_date] From [FileList] Order By [file_date]";
        	SQLiteDataAdapter db = new SQLiteDataAdapter(command);
        	db.Fill(dt);
        	
        	return dt;
        }
    }
}


/*

            slConnection = new SQLiteConnection(String.Format(slConnectionString, fileName));
            slConnection.Open();
 


            textBox1.Text += String.Format("start: {0}{1}", DateTime.Now.ToLongTimeString(), Environment.NewLine);

            var tran = slConnection.BeginTransaction();

            SQLiteCommand comm = slConnection.CreateCommand();

            for (int i = 1; i < 10000000; i++)
            {
                comm.Transaction = tran;
                comm.CommandText = "INSERT INTO test (id, identifier, x_created) VALUES (NULL, 'xxx', ?)";
                comm.Parameters.AddWithValue("@p1", DateTime.Now.ToString("s"));
                comm.CommandType = CommandType.Text;

                comm.ExecuteNonQuery();
            }

            tran.Commit();

            textBox1.Text += String.Format("end: {0}{1}", DateTime.Now.ToLongTimeString(), Environment.NewLine);

*/